SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

PRINT '---- CREATE TABLES'

--------------------------------------------------------------------
-- There is one central federation 'KNHB' in the Netherlands 
-- Federation 'KNHB' has URL 'www.knhb.nl'
--------------------------------------------------------------------
CREATE TABLE tblFederation
(   fed_Name		VARCHAR(20) NOT NULL,
	fed_Adres		VARCHAR(50) NOT NULL,
	fed_Zipcode		VARCHAR(7)  NOT NULL,
	fed_City		VARCHAR(20) NOT NULL,
	fed_Telephone	VARCHAR(20) NOT NULL,
	fed_Email		VARCHAR(40) NOT NULL,
	fed_URL			VARCHAR(40) NOT NULL,
    CONSTRAINT PK_fed_Name PRIMARY KEY (fed_Name),
)
--------------------------------------------------------------------
-- There is a province 'gelderland'
--------------------------------------------------------------------
CREATE TABLE tblProvince
(   
    Pro_province	VARCHAR(30) NOT NULL,
    CONSTRAINT PK_Pro_province PRIMARY KEY(Pro_province)  
)
--------------------------------------------------------------------
-- There is a season '2012-2013'
--------------------------------------------------------------------
CREATE TABLE tblSeasons
(	ses_Season	VARCHAR(20) NOT NULL,
    CONSTRAINT PK_ses_Season PRIMARY KEY(ses_Season),
)
--------------------------------------------------------------------
--------------------------------------------------------------------
-- There is a hockey club 'HVZ'
-- 'HVZ' has email adress 'Secretariaat@hvz.info' 
--------------------------------------------------------------------
CREATE TABLE tblClubs
(   clb_Name	    	VARCHAR(20) NOT NULL,
    clb_Longname		VARCHAR(80) NULL,
	clb_Adres			VARCHAR(50) NOT NULL,
	clb_Zipcode			VARCHAR(7)  NOT NULL,
	clb_City			VARCHAR(20) NOT NULL,
	clb_Province		VARCHAR(30) NOT NULL DEFAULT('Gelderland'),
	clb_Telephone		VARCHAR(20) NOT NULL,
	clb_Email			VARCHAR(40) NOT NULL,
	clb_URL				VARCHAR(40) NOT NULL,
	clb_Description		VARCHAR(MAX) NOT NULL,
    CONSTRAINT PK_clb_Name PRIMARY KEY (clb_Name),
    CONSTRAINT FK_Pro_Province FOREIGN KEY (clb_Province) 
    REFERENCES tblProvince (Pro_province) ON UPDATE CASCADE ON DELETE NO ACTION,

)
--------------------------------------------------------------------
-- Hockey club 'HVZ' has a complex 'Hengelder'
--------------------------------------------------------------------
CREATE TABLE tblComplex
(   cpx_Clubname	VARCHAR(20) NOT NULL,
    cpx_ComplexName	VARCHAR(20) NOT NULL,
	cpx_Adres		VARCHAR(50) NOT NULL,
	cpx_Zipcode		VARCHAR(7)  NOT NULL,
	cpx_City		VARCHAR(20) NOT NULL DEFAULT 'Zevenaar',
	cpx_Telephone	VARCHAR(20) NOT NULL,
	cpx_Description	VARCHAR(MAX) NULL,
    CONSTRAINT PK_cpx_Clubname PRIMARY KEY (cpx_Clubname,cpx_ComplexName),
    CONSTRAINT FK_cpx_Clubname FOREIGN KEY (cpx_Clubname) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
)
--------------------------------------------------------------------
-- There is a field type 'Gras'
--------------------------------------------------------------------
CREATE TABLE tblKindOfFields
(	kof_KindOfField	VARCHAR(20) NOT NULL,
	CONSTRAINT PK_kof_KindOfField PRIMARY KEY (kof_KindOfField),
)
--------------------------------------------------------------------
-- Hockey club 'HVZ' has a 'gras' field called 'Veld 1' on complex 'Hengelder'
--------------------------------------------------------------------
CREATE TABLE tblFields
(   fld_Clubname	VARCHAR(20) NOT NULL,
    fld_ComplexName	VARCHAR(20) NOT NULL,
    fld_Field		VARCHAR(10) NOT NULL,
    fld_KindOfField	VARCHAR(20) NOT NULL,
    CONSTRAINT PK_fld_Clubname PRIMARY KEY (fld_Clubname,fld_ComplexName,fld_Field),
    CONSTRAINT FK_fld_Clubname FOREIGN KEY (fld_Clubname,fld_ComplexName) 
    REFERENCES tblComplex (cpx_Clubname,cpx_ComplexName) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_fld_KindOfField FOREIGN KEY (fld_KindOfField) 
    REFERENCES tblKindOfFields (kof_KindOfField) ON UPDATE CASCADE ON DELETE NO ACTION,
)
GO

--------------------------------------------------------------------
-- At 'HVZ' there is a family 'Smith'
--------------------------------------------------------------------
CREATE TABLE tblFamilies
(	fam_ClubName		VARCHAR(20) NOT NULL,	
	fam_FamilyName		VARCHAR(80) NOT NULL,
	fam_Adres			VARCHAR(50) NOT NULL,
	fam_Zipcode			VARCHAR(7)  NOT NULL,
	fam_City			VARCHAR(20) NOT NULL,
	fam_Telephone		VARCHAR(20) NULL,
	fam_Email			VARCHAR(40) NULL,
	fam_BankAccount		VARCHAR(20) NULL,
    CONSTRAINT PK_fam_FamilyName PRIMARY KEY (fam_ClubName,fam_FamilyName),
    CONSTRAINT FK_fam_ClubName FOREIGN KEY (fam_ClubName) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
)
GO

--------------------------------------------------------------------
-- Ther is a sexe 'Man'
--------------------------------------------------------------------
CREATE TABLE tblSexes
(	sex_Sexe	VARCHAR(6) NOT NULL,
	CONSTRAINT PK_sex_Sexe PRIMARY KEY (sex_Sexe),
)
--------------------------------------------------------------------
-- There is a PersonState 'Suspended'
--------------------------------------------------------------------
CREATE TABLE tblPersonState
(   
    pss_State		VARCHAR(30) NOT NULL,
    CONSTRAINT PK_pss_State PRIMARY KEY(pss_State)  
)
--------------------------------------------------------------------
-- There is a person '1'
-- Person '1'has lastname 'Smith'
--------------------------------------------------------------------
CREATE TABLE tblPersons
(   prs_ID				INT IDENTITY NOT NULL,
    prs_Lastname		VARCHAR(60) NOT NULL,
	prs_MiddleName		VARCHAR(20) NULL,
    prs_Firstname		VARCHAR(30) NOT NULL,
    prs_Initials		VARCHAR(10) NOT NULL,
    prs_Sexe			VARCHAR(6)	NOT NULL,
    prs_DateofBirth		DATETIME	NOT NULL,
	prs_Telephone		VARCHAR(20) NULL,
    prs_Email			VARCHAR(80) NULL,
    prs_Username		VARCHAR(50) NULL,
	prs_FamilyClubName	VARCHAR(20) NOT NULL,	
	prs_FamilyName		VARCHAR(80) NOT NULL,
	prs_FederationNr	VARCHAR(20) NULL,
	prs_State			VARCHAR(30) NOT NULL DEFAULT('Aspirant lid'),
    prs_Remarks			VARCHAR(MAX) NULL,
    prs_Referee			BIT			NOT NULL DEFAULT (0),
	prs_RefereeNr		VARCHAR(20)	NULL,    
	prs_Active			BIT			NOT NULL DEFAULT (0),
    CONSTRAINT PK_prs_ID PRIMARY KEY(prs_ID),
    CONSTRAINT FK_prs_FamilyName FOREIGN KEY (prs_FamilyClubName,prs_FamilyName) 
    REFERENCES tblFamilies (fam_ClubName,fam_FamilyName) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_pss_PersonState FOREIGN KEY (prs_State) 
    REFERENCES tblPersonState (pss_State) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_prs_Sexe FOREIGN KEY (prs_Sexe) 
    REFERENCES tblSexes (sex_Sexe) ON UPDATE CASCADE ON DELETE NO ACTION,
	CONSTRAINT FK_prs_Users FOREIGN KEY (prs_Username)
	REFERENCES tblUFGUsers (usr_UserName) ON UPDATE CASCADE ON DELETE NO ACTION
)
GO
-- =============================================
-- Author:		R. van den Hoek
-- Description:	Clear prs_RefereeNr And monitor State
-- =============================================
CREATE TRIGGER [dbo].[tblPersons_Update]
   ON  [dbo].[tblPersons]
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @TranCount int
	SET @TranCount = @@TRANCOUNT 
	IF @TranCount > 0
		SAVE TRAN tblPersons_Update
	ELSE
		BEGIN TRAN
	
	BEGIN TRY
		

    -- Reset refereenr
	UPDATE tblPersons SET prs_RefereeNr = NULL
	WHERE prs_Referee = 0 AND prs_ID IN (SELECT prs_ID FROM deleted WHERE prs_Referee = 1)
	
	-- Set status is Speler
	UPDATE dbo.tblPersons SET prs_State = 'Speler' WHERE prs_Active = 1 AND prs_State IN ('Aangevraagd bond', 'Geroyeerd', 'Uitgeschreven') AND prs_ID IN (SELECT prs_ID FROM deleted WHERE prs_Active = 0)
	
	-- Remove Inactive person from teams and matches
	-- DELETE FROM tblMatchPlayers WHERE mpl_PersonID IN (SELECT prs_ID FROM inserted WHERE prs_Active = 0 OR prs_State = 'Geroyeerd') 
	-- DELETE FROM tblTeamMembers WHERE tmm_PersonID IN (SELECT prs_ID FROM inserted WHERE prs_Active = 0 OR prs_State = 'Geroyeerd') 
	
	-- Set status is Uitgeschreven
	UPDATE dbo.tblPersons SET prs_State = 'Uitgeschreven' WHERE prs_Active = 0 AND prs_State IN ('Aanvraag uitschrijving') AND prs_ID IN (SELECT prs_ID FROM deleted WHERE prs_Active = 1)	
	
	IF @TranCount = 0
		COMMIT TRAN	
		
	END TRY
	BEGIN CATCH
		DECLARE @ErrMsg nvarchar(2048)
		DECLARE @ErrSeverity int
		DECLARE @ErrState int

		SELECT @ErrMsg = ERROR_MESSAGE(),
			@ErrSeverity = ERROR_SEVERITY(),
			@ErrState = ERROR_STATE()

		IF @TranCount > 0
			ROLLBACK TRAN tblPersons_Update
		ELSE
			ROLLBACK TRAN	

		RAISERROR (@ErrMsg, @ErrSeverity, @ErrState)
	END CATCH
END
GO

--------------------------------------------------------------------
-- Voorzitters
--------------------------------------------------------------------
CREATE TABLE tblChairmen
(   chm_PersonID	INT			NOT NULL,
	chm_ClubName	VARCHAR(20) NOT NULL,
	CONSTRAINT PK_chm_PersonID PRIMARY KEY (chm_PersonID,chm_ClubName),
    CONSTRAINT FK_chm_PersonID FOREIGN KEY (chm_PersonID) 
    REFERENCES tblPersons (prs_ID),
    CONSTRAINT FK_chm_ClubName FOREIGN KEY (chm_ClubName) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
)

--------------------------------------------------------------------
-- Ledenadministratie
--------------------------------------------------------------------
CREATE TABLE tblMembersAdmin
(   mba_PersonID	INT			NOT NULL,
	mba_ClubName	VARCHAR(20) NOT NULL,
	CONSTRAINT PK_mba_PersonID PRIMARY KEY (mba_PersonID,mba_ClubName),
    CONSTRAINT FK_mba_PersonID FOREIGN KEY (mba_PersonID) 
    REFERENCES tblPersons (prs_ID),
    CONSTRAINT FK_mba_ClubName FOREIGN KEY (mba_ClubName) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
)

--------------------------------------------------------------------
-- Wedstrijdsecretaris
--------------------------------------------------------------------
CREATE TABLE tblOfficialScorer
(   ofs_PersonID	INT			NOT NULL,
	ofs_ClubName	VARCHAR(20) NOT NULL,
	CONSTRAINT PK_ofs_PersonID PRIMARY KEY (ofs_PersonID,ofs_ClubName),
    CONSTRAINT FK_ofs_PersonID FOREIGN KEY (ofs_PersonID) 
    REFERENCES tblPersons (prs_ID),
    CONSTRAINT FK_ofs_ClubName FOREIGN KEY (ofs_ClubName) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
)		

--------------------------------------------------------------------
-- Technische commissie
--------------------------------------------------------------------
CREATE TABLE tblTechnicalCommissioner
(   tco_PersonID	INT			NOT NULL,
	tco_ClubName	VARCHAR(20) NOT NULL,
	CONSTRAINT PK_tco_PersonID PRIMARY KEY (tco_PersonID,tco_ClubName),
    CONSTRAINT FK_tco_PersonID FOREIGN KEY (tco_PersonID) 
    REFERENCES tblPersons (prs_ID),
    CONSTRAINT FK_tco_ClubName FOREIGN KEY (tco_ClubName) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
)

--------------------------------------------------------------------
-- Scheidsrechters commissie
--------------------------------------------------------------------
CREATE TABLE tblOfficialsCommissioner
(   ofc_PersonID	INT			NOT NULL,
	ofc_ClubName	VARCHAR(20) NOT NULL,
	CONSTRAINT PK_ofc_PersonID PRIMARY KEY (ofc_PersonID,ofc_ClubName),
    CONSTRAINT FK_ofc_PersonID FOREIGN KEY (ofc_PersonID) 
    REFERENCES tblPersons (prs_ID),
    CONSTRAINT FK_ofc_ClubName FOREIGN KEY (ofc_ClubName) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
)
--------------------------------------------------------------------
-- There is a teamkind 'A'. 
-- Explanation: players are divided into players of age and/or level. Children and adolescents are divided acoording to age e.g. A is the age 16 and 17 years old.
--              For adolescents 'Heren' are young men, older men are put into 'Veteranen'. Men playing just for fun are 'Trimmers'
--------------------------------------------------------------------
CREATE TABLE tblKindsOfTeams
(	kot_TeamKind		VARCHAR(20) NOT NULL,
	kot_MaximumAge		INT			NOT NULL,
	kot_RefereeLevel	INT			NOT NULL,
    CONSTRAINT PK_kot_TeamKind PRIMARY KEY(kot_TeamKind),
)
--------------------------------------------------------------------
-- There is a class 'I'. 
-- Explanation: within the same KindOfTeams, which is more arranged according to age, classes are skill/playing levels
--------------------------------------------------------------------
CREATE TABLE tblClasses
(	cls_ClassName	VARCHAR(20) NOT NULL,
    CONSTRAINT PK_cls_ClassName PRIMARY KEY(cls_ClassName),
)
--------------------------------------------------------------------
-- In 2012-2013 we have a pool 'Oost' of kind 'A' of class 'II'
-- Explanation: the federation makes pools for kinds of teams divided into several classes. 
--              Because of travelling distances and the limited number of teams per pool, several pools are created.
--              Typical we have 4 - 20 teams per pool. 
--------------------------------------------------------------------
CREATE TABLE tblPools
(	pol_Season		VARCHAR(20) NOT NULL,
	pol_TeamKind	VARCHAR(20) NOT NULL,
	pol_ClassName	VARCHAR(20) NOT NULL,
	pol_PoolName	VARCHAR(20) NOT NULL,
    CONSTRAINT PK_pol_Season PRIMARY KEY(pol_Season,pol_TeamKind,pol_ClassName,pol_PoolName),
    CONSTRAINT FK_pol_Season FOREIGN KEY (pol_Season) 
    REFERENCES tblSeasons (ses_Season) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_pol_TeamKind FOREIGN KEY (pol_TeamKind) 
    REFERENCES tblKindsOfTeams (kot_TeamKind) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_pol_ClassName FOREIGN KEY (pol_ClassName) 
    REFERENCES tblClasses (cls_ClassName) ON UPDATE CASCADE ON DELETE NO ACTION,
)
--------------------------------------------------------------------
-- 'HVZ' has a team 'jA1' in '2012-2013'. 
-- Explanation: the federation puts each team in a pool
--------------------------------------------------------------------
CREATE TABLE tblTeams
(	tea_Clubname	VARCHAR(20) NOT NULL,			
	tea_Season		VARCHAR(20) NOT NULL,
	tea_TeamName	VARCHAR(20) NOT NULL,
	tea_PoolSeason	VARCHAR(20) NOT NULL,
	tea_TeamKind	VARCHAR(20) NOT NULL,
	tea_ClassName	VARCHAR(20) NOT NULL,
	tea_PoolName	VARCHAR(20) NOT NULL,
    CONSTRAINT PK_tea_Clubname PRIMARY KEY(tea_Clubname,tea_Season,tea_TeamName),
    CONSTRAINT FK_tea_Clubname FOREIGN KEY (tea_Clubname) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_tea_Season FOREIGN KEY (tea_Season) 
    REFERENCES tblSeasons (ses_Season) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_tea_PoolSeason FOREIGN KEY (tea_PoolSeason,tea_TeamKind,tea_ClassName,tea_PoolName) 
    REFERENCES tblPools (pol_Season,pol_TeamKind,pol_ClassName,pol_PoolName) ON UPDATE NO ACTION ON DELETE NO ACTION,
)
--------------------------------------------------------------------
-- Person '123' is a member of 'HVZ-jA1' in '2012-2013'
--------------------------------------------------------------------
CREATE TABLE tblTeamMembers
(   tmm_Clubname    VARCHAR(20) NOT NULL,
 	tmm_Season		VARCHAR(20) NOT NULL,
    tmm_TeamName    VARCHAR(20) NOT NULL,
    tmm_PersonID    INT			NOT NULL,
    CONSTRAINT PK_tmm_Clubname PRIMARY KEY (tmm_Clubname,tmm_Season,tmm_TeamName,tmm_PersonID),
    CONSTRAINT FK_tmm_Clubname_tmm_TeamName FOREIGN KEY (tmm_Clubname,tmm_Season,tmm_TeamName) 
    REFERENCES tblTeams (tea_Clubname,tea_Season,tea_TeamName) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_tmm_PersonID FOREIGN KEY (tmm_PersonID) 
    REFERENCES tblPersons (prs_ID),
)
--------------------------------------------------------------------
-- In '2012-2013' 'HVZ - jA1' playes against 'Westerduiven - jA1'
-- Explanation: In this example we have only two matches between teams, one teams playes in its home complex one time
--------------------------------------------------------------------
CREATE TABLE tblMatches
(	mat_HomeClubname	VARCHAR(20) NOT NULL,			
	mat_HomeTeamName	VARCHAR(20) NOT NULL,
	mat_AgainstClubname	VARCHAR(20) NOT NULL,			
	mat_AgainstTeamName	VARCHAR(20) NOT NULL,
	mat_AgainstSeason	VARCHAR(20) NOT NULL,
	mat_HomeSeason		VARCHAR(20) NOT NULL,
	mat_Date			DATE		NOT NULL,
	mat_From			TIME		NULL,
	mat_To				TIME		NULL,
	mat_Clubname		VARCHAR(20) NOT NULL,
    mat_ComplexName		VARCHAR(20) NULL,
    mat_Field			VARCHAR(10) NULL,
    mat_homegoals		VARCHAR(10) NULL,
    mat_againstgoals		VARCHAR(10) NULL,
    mat_Description		VARCHAR(MAX) NULL,
    CONSTRAINT PK_mat_HomeClubname PRIMARY KEY (mat_HomeClubname,mat_HomeSeason,mat_HomeTeamName,mat_AgainstClubname,mat_AgainstSeason,mat_AgainstTeamName),
    CONSTRAINT FK_mat_HomeClubname FOREIGN KEY (mat_HomeClubname,mat_HomeSeason,mat_HomeTeamName) 
    REFERENCES tblTeams (tea_Clubname,tea_Season,tea_TeamName) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_mat_AgainstClubname FOREIGN KEY (mat_AgainstClubname,mat_AgainstSeason,mat_AgainstTeamName) 
    REFERENCES tblTeams (tea_Clubname,tea_Season,tea_TeamName),
    CONSTRAINT FK_mat_Clubname FOREIGN KEY (mat_Clubname,mat_ComplexName,mat_Field) 
    REFERENCES tblFields (fld_Clubname,fld_ComplexName,fld_Field),
	CONSTRAINT CK_mat_Season_Same CHECK (mat_HomeSeason = mat_AgainstSeason)
)
--------------------------------------------------------------------
-- There is a referee role 'Scheidsrechter'
--------------------------------------------------------------------
CREATE TABLE tblMatchRefereeRoles
(	mrr_RoleName	VARCHAR(20) NOT NULL,
    CONSTRAINT PK_mrr_RoleName PRIMARY KEY(mrr_RoleName),
)
--------------------------------------------------------------------
-- The match '2012-2013' 'HVZ - jA1' against 'Westerduiven - jA1' is refereed by '1' as 'Scheidsrechter'
--------------------------------------------------------------------
CREATE TABLE tblMatchReferees
(	mrf_HomeClubname	VARCHAR(20) NOT NULL,			
	mrf_HomeSeason		VARCHAR(20) NOT NULL,
	mrf_HomeTeamName	VARCHAR(20) NOT NULL,
	mrf_AgainstClubname	VARCHAR(20) NOT NULL,			
	mrf_AgainstSeason	VARCHAR(20) NOT NULL,
	mrf_AgainstTeamName	VARCHAR(20) NOT NULL,
    mrf_PersonID		INT			NOT NULL,
    mrf_RoleName		VARCHAR(20) NOT NULL,
    CONSTRAINT PK_mrf_HomeClubname PRIMARY KEY (mrf_HomeClubname,mrf_HomeSeason,mrf_HomeTeamName,mrf_AgainstClubname,mrf_AgainstSeason,mrf_AgainstTeamName,mrf_PersonID),
    CONSTRAINT FK_mrf_HomeClubname FOREIGN KEY (mrf_HomeClubname,mrf_HomeSeason,mrf_HomeTeamName,mrf_AgainstClubname,mrf_AgainstSeason,mrf_AgainstTeamName) 
    REFERENCES tblMatches (mat_HomeClubname,mat_HomeSeason,mat_HomeTeamName,mat_AgainstClubname,mat_AgainstSeason,mat_AgainstTeamName) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_mrf_PersonID FOREIGN KEY (mrf_PersonID) 
    REFERENCES tblPersons (prs_ID),
    CONSTRAINT FK_mrf_RoleName FOREIGN KEY (mrf_RoleName) 
    REFERENCES tblMatchRefereeRoles (mrr_RoleName),
)
--------------------------------------------------------------------
-- In match '2012-2013' 'HVZ - jA1' against 'Westerduiven - jA1' '1' is a player
--------------------------------------------------------------------
CREATE TABLE tblMatchPlayers
(	mpl_HomeClubname	VARCHAR(20) NOT NULL,			
	mpl_HomeSeason		VARCHAR(20) NOT NULL,
	mpl_HomeTeamName	VARCHAR(20) NOT NULL,
	mpl_AgainstClubname	VARCHAR(20) NOT NULL,			
	mpl_AgainstSeason	VARCHAR(20) NOT NULL,
	mpl_AgainstTeamName	VARCHAR(20) NOT NULL,
	mpl_Clubname		VARCHAR(20) NOT NULL,
 	mpl_Season			VARCHAR(20) NOT NULL,
    mpl_TeamName		VARCHAR(20) NOT NULL,
    mpl_PersonID		INT			NOT NULL,    
    CONSTRAINT PK_mpl_HomeClubname PRIMARY KEY (mpl_HomeClubname,mpl_HomeSeason,mpl_HomeTeamName,mpl_AgainstClubname,mpl_AgainstSeason,mpl_AgainstTeamName,mpl_Clubname,mpl_Season,mpl_TeamName,mpl_PersonID),
    CONSTRAINT FK_mpl_HomeClubname FOREIGN KEY (mpl_HomeClubname,mpl_HomeSeason,mpl_HomeTeamName,mpl_AgainstClubname,mpl_AgainstSeason,mpl_AgainstTeamName) 
    REFERENCES tblMatches (mat_HomeClubname,mat_HomeSeason,mat_HomeTeamName,mat_AgainstClubname,mat_AgainstSeason,mat_AgainstTeamName) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_mpl_Clubname FOREIGN KEY (mpl_Clubname,mpl_Season,mpl_TeamName,mpl_PersonID) 
    REFERENCES tblTeamMembers (tmm_Clubname,tmm_Season,tmm_TeamName,tmm_PersonID),
)
GO
--------------------------------------------------------------------
-- There is a card type 'Rood'
--------------------------------------------------------------------
CREATE TABLE tblKindOfCards
(	koc_Card	VARCHAR(20) NOT NULL,
    CONSTRAINT PK_koc_Card PRIMARY KEY(koc_Card),
)
--------------------------------------------------------------------
-- In match '2012-2013' 'HVZ - jA1' against 'Westerduiven - jA1'player '1' has got card 'Rood' at  playing time '23:00'
--------------------------------------------------------------------
CREATE TABLE tblCards
(	crd_HomeClubname	VARCHAR(20) NOT NULL,			
	crd_HomeSeason		VARCHAR(20) NOT NULL,
	crd_HomeTeamName	VARCHAR(20) NOT NULL,
	crd_AgainstClubname	VARCHAR(20) NOT NULL,			
	crd_AgainstSeason	VARCHAR(20) NOT NULL,
	crd_AgainstTeamName	VARCHAR(20) NOT NULL,
	crd_Clubname		VARCHAR(20) NOT NULL,
 	crd_Season			VARCHAR(20) NOT NULL,
    crd_TeamName		VARCHAR(20) NOT NULL,
    crd_PersonID		INT			NOT NULL,
    crd_Card			VARCHAR(20) NOT NULL,
    crd_Time			TIME		NOT NULL,
    crd_NrOfSuspension	INT			NULL,
    CONSTRAINT PK_crd_HomeClubname PRIMARY KEY (crd_HomeClubname,crd_HomeSeason,crd_HomeTeamName,crd_AgainstClubname,crd_AgainstSeason,crd_AgainstTeamName,crd_Clubname,crd_Season,crd_TeamName,crd_PersonID,crd_Card),
    CONSTRAINT FK_crd_HomeClubname FOREIGN KEY (crd_HomeClubname,crd_HomeSeason,crd_HomeTeamName,crd_AgainstClubname,crd_AgainstSeason,crd_AgainstTeamName,crd_Clubname,crd_Season,crd_TeamName,crd_PersonID) 
	REFERENCES tblMatchPlayers (mpl_HomeClubname,mpl_HomeSeason,mpl_HomeTeamName,mpl_AgainstClubname,mpl_AgainstSeason,mpl_AgainstTeamName,mpl_Clubname,mpl_Season,mpl_TeamName,mpl_PersonID) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_crd_Card FOREIGN KEY (crd_Card)
    REFERENCES tblKindOfCards (koc_Card) ON UPDATE CASCADE ON DELETE NO ACTION
)
--------------------------------------------------------------------
-- At 'HVZ' there is a task 'Bardienst'
--------------------------------------------------------------------
CREATE TABLE tblTasks
(	tsk_Clubname	VARCHAR(20) NOT NULL,
	tsk_Name		VARCHAR(20) NOT NULL,
    CONSTRAINT PK_tsk_Name PRIMARY KEY(tsk_Clubname,tsk_Name),
    CONSTRAINT FK_tsk_Clubname FOREIGN KEY (tsk_Clubname) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
)
--------------------------------------------------------------------
--------------------------------------------------------------------
-- '2012-11-1' person '123' has 'Bardienst' from '10:00' until '12:00'
--------------------------------------------------------------------
CREATE TABLE tblVolunteerTasks
(	vsk_Clubname	VARCHAR(20) NOT NULL,
	vsk_TaskName	VARCHAR(20) NOT NULL,
	vsk_PersonID	INT NOT NULL,
	vsk_Date		DATE NOT NULL,
	vsk_From		TIME NOT NULL,
	vsk_To			TIME NOT NULL,
    CONSTRAINT PK_vsk_TaskName PRIMARY KEY(vsk_Clubname,vsk_TaskName,vsk_PersonID,vsk_Date,vsk_From,vsk_To),
    CONSTRAINT FK_vsk_Clubname FOREIGN KEY (vsk_Clubname,vsk_TaskName) 
    REFERENCES tblTasks (tsk_Clubname,tsk_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
)
----------------------------------------------------------------------
-- ContributionFees
-- In season 2012 the contribution fee for club HVZ  for a member of kind A is 100 Euro
----------------------------------------------------------------------
CREATE TABLE tblContributionFees
(	cnt_Clubname	VARCHAR(20) NOT NULL,
	cnt_Season		VARCHAR(20) NOT NULL,
	cnt_Kind		VARCHAR(20) NOT NULL,
	cnt_Fee			NUMERIC(5,1) NOT NULL,
	CONSTRAINT PK_cnt_Clubname PRIMARY KEY (cnt_Clubname,cnt_Season,cnt_Kind),
    CONSTRAINT FK_cnt_Clubname FOREIGN KEY (cnt_Clubname) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_cnt_Season FOREIGN KEY (cnt_Season) 
    REFERENCES tblSeasons (ses_Season) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_cnt_Kind FOREIGN KEY (cnt_Kind) 
    REFERENCES tblKindsOfTeams (kot_TeamKind) ON UPDATE CASCADE ON DELETE NO ACTION,
)


--------------------------------------------------------------------
-- There is a Discount 'Amount'
--------------------------------------------------------------------
CREATE TABLE tbldiscounttype
(   
    dct_Type		VARCHAR(20) NOT NULL,
    CONSTRAINT PK_dct_Type PRIMARY KEY(dct_Type)  
)
--------------------------------------------------------------------
----------------------------------------------------------------------
-- ContributionDiscount
-- In season 2012 the contribution discount for club HVZ is none/amount/percentage
----------------------------------------------------------------------

CREATE TABLE tblContributionDiscounts
(	cdc_Clubname	VARCHAR(20) NOT NULL,
	cdc_Season		VARCHAR(20) NOT NULL,
	cdc_Discount	VARCHAR(20) NOT NULL, 
	CONSTRAINT PK_cdc_Clubname PRIMARY KEY (cdc_Clubname,cdc_Season),
    CONSTRAINT FK_cdc_Clubname FOREIGN KEY (cdc_Clubname) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_cdc_Season FOREIGN KEY (cdc_Season) 
    REFERENCES tblSeasons (ses_Season) ON UPDATE CASCADE ON DELETE NO ACTION,
	CONSTRAINT FK_cdc_Discount FOREIGN KEY (cdc_Discount) 
    REFERENCES tbldiscounttype (dct_Type) ON UPDATE CASCADE ON DELETE NO ACTION,
)
----------------------------------------------------------------------
-- ContributionDiscountMember
-- In season 2012 the contribution discount for club HVZ for member number 3 in the same family is 20
----------------------------------------------------------------------
CREATE TABLE tblContributionDiscountMember
(	cdm_Clubname	VARCHAR(20) NOT NULL,
	cdm_Season		VARCHAR(20) NOT NULL,
	cdm_Number		INT NOT NULL,
	cdm_Amount		NUMERIC(4,2) NOT NULL,
	CONSTRAINT PK_cdm_Clubname PRIMARY KEY (cdm_Clubname,cdm_Season,cdm_Number),
    CONSTRAINT FK_cdm_Clubname FOREIGN KEY (cdm_Clubname) 
    REFERENCES tblClubs (clb_Name) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT FK_cdm_Season FOREIGN KEY (cdm_Season) 
    REFERENCES tblSeasons (ses_Season) ON UPDATE CASCADE ON DELETE NO ACTION,
)
GO

